{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "229eed8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import re\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "986d069d",
   "metadata": {},
   "outputs": [],
   "source": [
    "os.chdir('../../lecture-python/raw-data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "56825957",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['baby_trade_history.csv',\n",
       " 'bond_intraday_trade.csv',\n",
       " 'cbond-interest-info.xlsx',\n",
       " 'cfps2018_famconf_demo.csv',\n",
       " 'meal_order_detail.xlsx',\n",
       " 'sam_tianchi_mum_baby.csv',\n",
       " 'titanic.csv',\n",
       " 'titanic.xlsx',\n",
       " 'titanic2.csv']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "58173bb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "cbond = pd.read_excel('cbond-interest-info.xlsx')\n",
    "cbond.columns = ['code','name','interest-info']\n",
    "cbond.drop(index=[0],inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "69079584",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>interest-info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>110043.SH</td>\n",
       "      <td>无锡转债</td>\n",
       "      <td>第一年0.30%、第二年0.50%、第三年0.80%、第四年1.00%、第五年1.30%、第...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>110044.SH</td>\n",
       "      <td>广电转债</td>\n",
       "      <td>本次发行的可转债票面利率：第一年0.40%、第二年0.60%、第三年1.00%、第四年1.5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>110045.SH</td>\n",
       "      <td>海澜转债</td>\n",
       "      <td>第一年为0.3%、第二年为0.5%、第三年为0.8%、第四年为1.0%、第五年为1.3%、第...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>110046.SH</td>\n",
       "      <td>山鹰转债</td>\n",
       "      <td>第一年0.5%、第二年0.8%、第三年1.0%、第四年1.5%、第五年1.8%、第六年2.0%。</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>110047.SH</td>\n",
       "      <td>福能转债</td>\n",
       "      <td>第一年0.4%、第二年0.6%、第三年1.0%、第四年1.5%、第五年2.0%、第六年3.0%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        code  name                                      interest-info\n",
       "1  110043.SH  无锡转债  第一年0.30%、第二年0.50%、第三年0.80%、第四年1.00%、第五年1.30%、第...\n",
       "2  110044.SH  广电转债  本次发行的可转债票面利率：第一年0.40%、第二年0.60%、第三年1.00%、第四年1.5...\n",
       "3  110045.SH  海澜转债  第一年为0.3%、第二年为0.5%、第三年为0.8%、第四年为1.0%、第五年为1.3%、第...\n",
       "4  110046.SH  山鹰转债   第一年0.5%、第二年0.8%、第三年1.0%、第四年1.5%、第五年1.8%、第六年2.0%。\n",
       "5  110047.SH  福能转债    第一年0.4%、第二年0.6%、第三年1.0%、第四年1.5%、第五年2.0%、第六年3.0%"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cbond.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6eb3b70b",
   "metadata": {},
   "outputs": [],
   "source": [
    "regx = \"(\\d{1,2}\\.\\d{1,2}%)\"\n",
    "cbond['info'] = cbond['interest-info'].apply(lambda x: re.findall(regx, x) if re.findall(regx, x) else re.findall('\\d+%', x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d7e0eb65",
   "metadata": {},
   "outputs": [],
   "source": [
    "years = ['一', '二', '三', '四', '五', '六']\n",
    "for i in range(6):\n",
    "    col = \"第{}年\".format(years[i])\n",
    "    cbond[col] = cbond['info'].apply(lambda x: x[i] if i<len(x) else pd.NA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "86525953",
   "metadata": {},
   "outputs": [],
   "source": [
    "cbond.drop([\"interest-info\",'info'], axis=1).to_csv('HW-re-exercise.csv', encoding='utf_8_sig')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8298d5e0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>第一年</th>\n",
       "      <th>第二年</th>\n",
       "      <th>第三年</th>\n",
       "      <th>第四年</th>\n",
       "      <th>第五年</th>\n",
       "      <th>第六年</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>110043.SH</td>\n",
       "      <td>无锡转债</td>\n",
       "      <td>0.30%</td>\n",
       "      <td>0.50%</td>\n",
       "      <td>0.80%</td>\n",
       "      <td>1.00%</td>\n",
       "      <td>1.30%</td>\n",
       "      <td>1.80%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>110044.SH</td>\n",
       "      <td>广电转债</td>\n",
       "      <td>0.40%</td>\n",
       "      <td>0.60%</td>\n",
       "      <td>1.00%</td>\n",
       "      <td>1.50%</td>\n",
       "      <td>1.80%</td>\n",
       "      <td>2.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>110045.SH</td>\n",
       "      <td>海澜转债</td>\n",
       "      <td>0.3%</td>\n",
       "      <td>0.5%</td>\n",
       "      <td>0.8%</td>\n",
       "      <td>1.0%</td>\n",
       "      <td>1.3%</td>\n",
       "      <td>1.8%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>110046.SH</td>\n",
       "      <td>山鹰转债</td>\n",
       "      <td>0.5%</td>\n",
       "      <td>0.8%</td>\n",
       "      <td>1.0%</td>\n",
       "      <td>1.5%</td>\n",
       "      <td>1.8%</td>\n",
       "      <td>2.0%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>110047.SH</td>\n",
       "      <td>福能转债</td>\n",
       "      <td>0.4%</td>\n",
       "      <td>0.6%</td>\n",
       "      <td>1.0%</td>\n",
       "      <td>1.5%</td>\n",
       "      <td>2.0%</td>\n",
       "      <td>3.0%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>420</th>\n",
       "      <td>128141.SZ</td>\n",
       "      <td>旺能转债</td>\n",
       "      <td>0.30%</td>\n",
       "      <td>0.50%</td>\n",
       "      <td>1.00%</td>\n",
       "      <td>1.50%</td>\n",
       "      <td>1.80%</td>\n",
       "      <td>2.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>421</th>\n",
       "      <td>128142.SZ</td>\n",
       "      <td>新乳转债</td>\n",
       "      <td>0.30%</td>\n",
       "      <td>0.50%</td>\n",
       "      <td>1.00%</td>\n",
       "      <td>1.50%</td>\n",
       "      <td>1.80%</td>\n",
       "      <td>2.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>422</th>\n",
       "      <td>128143.SZ</td>\n",
       "      <td>锋龙转债</td>\n",
       "      <td>0.50%</td>\n",
       "      <td>0.70%</td>\n",
       "      <td>1.20%</td>\n",
       "      <td>1.80%</td>\n",
       "      <td>2.50%</td>\n",
       "      <td>3.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>423</th>\n",
       "      <td>128144.SZ</td>\n",
       "      <td>利民转债</td>\n",
       "      <td>0.30%</td>\n",
       "      <td>0.50%</td>\n",
       "      <td>0.80%</td>\n",
       "      <td>1.00%</td>\n",
       "      <td>1.50%</td>\n",
       "      <td>2.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>424</th>\n",
       "      <td>128145.SZ</td>\n",
       "      <td>日丰转债</td>\n",
       "      <td>0.3%</td>\n",
       "      <td>0.6%</td>\n",
       "      <td>1.5%</td>\n",
       "      <td>2.5%</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>424 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          code  name    第一年    第二年    第三年    第四年    第五年    第六年\n",
       "1    110043.SH  无锡转债  0.30%  0.50%  0.80%  1.00%  1.30%  1.80%\n",
       "2    110044.SH  广电转债  0.40%  0.60%  1.00%  1.50%  1.80%  2.00%\n",
       "3    110045.SH  海澜转债   0.3%   0.5%   0.8%   1.0%   1.3%   1.8%\n",
       "4    110046.SH  山鹰转债   0.5%   0.8%   1.0%   1.5%   1.8%   2.0%\n",
       "5    110047.SH  福能转债   0.4%   0.6%   1.0%   1.5%   2.0%   3.0%\n",
       "..         ...   ...    ...    ...    ...    ...    ...    ...\n",
       "420  128141.SZ  旺能转债  0.30%  0.50%  1.00%  1.50%  1.80%  2.00%\n",
       "421  128142.SZ  新乳转债  0.30%  0.50%  1.00%  1.50%  1.80%  2.00%\n",
       "422  128143.SZ  锋龙转债  0.50%  0.70%  1.20%  1.80%  2.50%  3.00%\n",
       "423  128144.SZ  利民转债  0.30%  0.50%  0.80%  1.00%  1.50%  2.00%\n",
       "424  128145.SZ  日丰转债   0.3%   0.6%   1.5%   2.5%   <NA>   <NA>\n",
       "\n",
       "[424 rows x 8 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cbond.drop([\"interest-info\",'info'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "719341ad",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
